<?php

/**
 * This is the model class for table "matchs".
 *
 * The followings are the available columns in table 'matchs':
 * @property integer $match_id
 * @property integer $match_player_win_1
 * @property integer $match_player_win_2
 * @property integer $match_player_lost_1
 * @property integer $match_player_lost_2
 * @property string $match_date
 * @property integer $match_point_win
 * @property integer $match_point_lost
 * @property integer $match_price
 */
class Matchs extends CActiveRecord
{

  /**
   * @return string the associated database table name
   */
  public function tableName()
  {
    return 'matchs';
  }

  /**
   * @return array validation rules for model attributes.
   */
  public function rules()
  {
    // NOTE: you should only define rules for those attributes that
    // will receive user inputs.
    return array(
        array('match_player_win_1, match_player_win_2, match_player_lost_1, match_player_lost_2, match_point_win, match_point_lost, match_price', 'numerical', 'integerOnly' => true),
        array('match_date', 'safe'),
        // The following rule is used by search().
        // @todo Please remove those attributes that should not be searched.
        array('match_id, match_player_win_1, match_player_win_2, match_player_lost_1, match_player_lost_2, match_date, match_point_win, match_point_lost, match_price', 'safe', 'on' => 'search'),
    );
  }

  /**
   * @return array relational rules.
   */
  public function relations()
  {
    // NOTE: you may need to adjust the relation name and the related
    // class name for the relations automatically generated below.
    return array(
    );
  }

  /**
   * @return array customized attribute labels (name=>label)
   */
  public function attributeLabels()
  {
    return array(
        'match_id' => 'Match',
        'match_player_win_1' => 'Match Player Win 1',
        'match_player_win_2' => 'Match Player Win 2',
        'match_player_lost_1' => 'Match Player Lost 1',
        'match_player_lost_2' => 'Match Player Lost 2',
        'match_date' => 'Match Date',
        'match_point_win' => 'Match Point Win',
        'match_point_lost' => 'Match Point Lost',
        'match_price' => 'Match Price',
    );
  }

  /**
   * Retrieves a list of models based on the current search/filter conditions.
   *
   * Typical usecase:
   * - Initialize the model fields with values from filter form.
   * - Execute this method to get CActiveDataProvider instance which will filter
   * models according to data in model fields.
   * - Pass data provider to CGridView, CListView or any similar widget.
   *
   * @return CActiveDataProvider the data provider that can return the models
   * based on the search/filter conditions.
   */
  public function search()
  {
    // @todo Please modify the following code to remove attributes that should not be searched.

    $criteria = new CDbCriteria;

    $criteria->compare('match_id', $this->match_id);
    $criteria->compare('match_player_win_1', $this->match_player_win_1);
    $criteria->compare('match_player_win_2', $this->match_player_win_2);
    $criteria->compare('match_player_lost_1', $this->match_player_lost_1);
    $criteria->compare('match_player_lost_2', $this->match_player_lost_2);
    $criteria->compare('match_date', $this->match_date, true);
    $criteria->compare('match_point_win', $this->match_point_win);
    $criteria->compare('match_point_lost', $this->match_point_lost);
    $criteria->compare('match_price', $this->match_price);

    return new CActiveDataProvider($this, array(
        'criteria' => $criteria,
    ));
  }

  /**
   * Returns the static model of the specified AR class.
   * Please note that you should have this exact method in all your CActiveRecord descendants!
   * @param string $className active record class name.
   * @return Matchs the static model class
   */
  public static function model($className = __CLASS__)
  {
    return parent::model($className);
  }

  public function getAll($month = NULL)
  {
    $user = Yii::app()->db->createCommand()
            ->select(
                    'match_id', 'match_player_win_1', 'match_player_win_2', 'match_player_lost_1', 'match_player_lost_2', 'match_date', 'match_point_win', 'match_point_lost', 'match_price'
            )
            ->from('matchs')
            ->join('members', 'matchs.match_player_win_1 = members.member_id')
//    ->where('id=:id', array(':id'=>$id))
            ->query();

    return $user;
  }

  public function getWinByMemberId($memberId)
  {
    $list = Yii::app()->db->createCommand()
                    ->select(array(
                        'match_id',
                        'match_player_win_1',
                        'match_player_win_2',
                        'match_player_lost_1',
                        'match_player_lost_2',
                        'match_date',
                        'match_point_win',
                        'match_point_lost',
                        'match_price')
                    )
                    ->from('matchs')
//    ->join('members', 'matchs.match_player_win_1 = members.member_id')
                    ->where('match_player_win_1 = :match_player_win_1 OR match_player_win_2 = :match_player_win_2', array(':match_player_win_1' => $memberId, ':match_player_win_2' => $memberId))
//    ->where('match_player_win_2=:match_player_win_2', array(':match_player_win_2'=>$memberId))
                    ->query()->readAll();
    return $list;
  }

  public function getLostByMemberId($memberId)
  {
    $list = Yii::app()->db->createCommand()
                    ->select(array(
                        'match_id',
                        'match_player_win_1',
                        'match_player_win_2',
                        'match_player_lost_1',
                        'match_player_lost_2',
                        'match_date',
                        'match_point_win',
                        'match_point_lost',
                        'match_price')
                    )
                    ->from('matchs')
//    ->join('members', 'matchs.match_player_win_1 = members.member_id')
                    ->where('match_player_lost_1 = :match_player_lost_1 OR match_player_win_2 = :match_player_lost_2', array(':match_player_lost_1' => $memberId, ':match_player_lost_2' => $memberId))
//    ->where('match_player_win_2=:match_player_win_2', array(':match_player_win_2'=>$memberId))
                    ->query()->readAll();
    return $list;
  }

  public function getRankByMemberId($memberId)
  {
    $connection = Yii::app()->db;   // assuming you have configured a "db" connection
// If not, you may explicitly create a connection:
// $connection=new CDbConnection($dsn,$username,$password);
    $sql = 'select
                (select count(*) *3 - (select count(*)
                                 from matchs
                                 where
                                 (matchs.match_player_lost_1= '.$memberId.' or matchs.match_player_lost_2 ='.$memberId.')
                                 AND matchs.match_date <= master.match_date)
                from matchs
                where
                  (matchs.match_player_win_1 = '.$memberId.' or matchs.match_player_win_2 = '.$memberId.')
                   AND matchs.match_date <= master.match_date
                ) As rank
             from matchs As master
             group by master.match_date

                ';
    $command = $connection->createCommand($sql);
    $data = $command->query()->readAll();

    return $data;
  }

}
